package sf.dsl;

import sf.core.DBField;
import sf.database.dao.DBContext;
import sf.database.dialect.DBDialect;
import sf.database.jdbc.sql.SQLContext;
import sf.database.jdbc.sql.SQLParameter;
import sf.database.meta.ColumnMapping;
import sf.database.util.OrmUtils;
import sf.database.util.SimpleSQLTemplate;
import sf.dsl.example.SelectOpt;
import sf.spring.util.CollectionUtils;
import sf.tools.StringUtils;

import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class ExampleSQLHelp {
    public static String getColumn(Example example, boolean useAlias) {
        StringBuilder sb = new StringBuilder();
        if (CollectionUtils.isNotEmpty(example.getSelectColumns())) {
            Map<DBField, ColumnMapping> schemaMap = example.table.getSchemaMap();
            for (DBField field : example.getSelectColumns()) {
                sb.append(sb.length() > 0 ? "," : "").append(example.getColumnAlias(example.dialect.wrapKeyword(schemaMap.get(field).getRawColumnName()), useAlias));
            }
        }
//        else if(example.countColumn!=null){
//            Map<DBField, ColumnMapping> schemaMap = example.table.getSchemaMap();
//            sb.append(" count(").append(example.dialect.wrapKeyword(schemaMap.get(example.countColumn).rawColumnName)).append(") ");
//        }
        return sb.toString();
    }

    public static String getCountColumn(Example example, boolean useAlias) {
        StringBuilder sb = new StringBuilder();
        if (example.countColumn != null) {
            Map<DBField, ColumnMapping> schemaMap = example.table.getSchemaMap();
            sb.append(" count(").append(example.getColumnAlias(example.dialect.wrapKeyword(schemaMap.get(example.countColumn).getRawColumnName()), useAlias)).append(") ");
        }
        return sb.toString();
    }

    /**
     * 获取完整的查询
     * @param example
     * @param context
     * @param opt         查询操作
     * @param offsetLimit 是否使用offsetLimit
     * @param useAlias    是否使用别名
     * @return
     */
    public static SQLContext getSelectSQLContext(Example example, DBContext context, SelectOpt opt, boolean offsetLimit, boolean useAlias) {
        String selectClause = getSelectClause(example, opt, useAlias);
        String fromClause = getFromClause(example, context, useAlias);
        SQLContext SQLContext = getSQLContextWhereClause(example, useAlias);
        String whereClause = SQLContext.getSql();
        String groupByCaluse = getGroupByClause(example, useAlias);
        String orderByCaluse = getOrderByClause(example, useAlias);

        StringBuilder sql = new StringBuilder();
        sql.append(selectClause).append(" from ").append(fromClause);
        if (StringUtils.isNotBlank(whereClause)) {
            sql.append(" where ").append(whereClause);
        }
        if (StringUtils.isNotBlank(groupByCaluse)) {
            sql.append(" ").append(groupByCaluse);
            Example.Criterion c = example.getHaving();
            if (c != null) {
                if (StringUtils.isNotBlank(c.getCondition())) {
                    sql.append(" ").append(c.getCondition());
                }
                if (c.getValue() != null) {
                    for (Object obj : (Collection<?>) c.getValue()) {
                        SQLContext.getParas().add(new SQLParameter(obj, null));
                    }
                }
            }
        }
        if (StringUtils.isNotBlank(orderByCaluse)) {
            sql.append(" ").append(orderByCaluse);
        }
        if (offsetLimit && example.getOffsetLimit() != null) {
            SQLContext.setSql(example.dialect.sqlPageList(new StringBuilder(sql.toString()), example.getOffsetLimit().getOffset(), example.getOffsetLimit().getLimit()).toString());
        } else {
            SQLContext.setSql(sql.toString());
        }

        if (opt == SelectOpt.page) {
            String selectCount = getSelectClause(example, SelectOpt.count, useAlias);
            StringBuilder countSql = new StringBuilder();
            countSql.append(selectCount).append(" from ").append(fromClause);
            if (StringUtils.isNotBlank(whereClause)) {
                countSql.append(" where ").append(whereClause);
            }
            SQLContext.setCountSql(countSql.toString());
            SQLContext.setListSql(SQLContext.getSql());
            SQLContext.setSql(null);
        }
        return SQLContext;
    }

    public static String getSelectClause(Example example, SelectOpt opt, boolean useAlias) {
        if (opt != null) {
            switch (opt) {
                case count:
                    if (example.getCountColumn() != null) {
                        return "select " + getCountColumn(example, useAlias);
                    } else {
                        return "select count(*) ";
                    }
                case exists:
                    return "select 1 ";
                default:
                    break;
            }
        }
        Set<DBField> selectColumns = example.getSelectColumns();
        if (CollectionUtils.isNotEmpty(selectColumns) || StringUtils.isNotBlank(example.select)) {
            StringBuilder selectSql = new StringBuilder();
            selectSql.append("select ");
            if (example.isDistinct()) {
                selectSql.append("distinct ");
            }
            if (CollectionUtils.isNotEmpty(selectColumns)) {
                selectSql.append(getColumn(example, useAlias));
            }
            if (StringUtils.isNotBlank(example.select)) {
                selectSql.append(CollectionUtils.isNotEmpty(selectColumns) ? "," : "").append(SimpleSQLTemplate.unwrapper(example.dialect, example.select));
            }
            return selectSql.toString();
        }
        return "select " + example.getColumnAlias("*", useAlias);
    }

    public static String getFromClause(Example example, DBContext context, boolean useAlias) {
        String tableName = example.tableName;
        String from = example.from;
        if (from == null) {
            from = "";
        } else {
            from = " " + from;
        }
        if (StringUtils.isBlank(tableName)) {
            tableName = OrmUtils.getDynamicTableName(context, example.table);
        }
        if (example.dialect != null) {
            return example.getTableNameAlias(example.dialect.wrapKeyword(tableName), useAlias) + from;
        }
        return example.getTableNameAlias(tableName, useAlias) + from;
    }

    public static SQLContext getSQLContextWhereClause(Example example, boolean useAlias) {
        SQLContext context = new SQLContext();
        List<Example.Criteria> criteriaList = example.criterias;
        boolean useBasicType = example.isUseBasicType();
        StringBuilder sb = new StringBuilder();
        List<SQLParameter> parameters = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(criteriaList)) {
            boolean flag = false;
            for (Example.Criteria criteria : criteriaList) {
                if (criteria.isValid() && CollectionUtils.isNotEmpty(criteria.criteria)) {
                    if (flag) {
                        sb.append(" ").append(criteria.andOr).append(" (");
                    }
                    for (Example.Criterion criterion : criteria.criteria) {
                        switch (criterion.getValueType()) {
                            case noValue:
                                sb.append(getSqlFragment(example, criterion, useAlias));
                                break;
                            case singleValue:
                                sb.append(getSqlFragment(example, criterion, useAlias));
                                parameters.add(getSQLParameterByUseBasicType(useBasicType, criterion.getValue(), criterion.getColumn()));
                                break;
                            case arrayValue:
                                sb.append(getSqlFragment(example, criterion, useAlias));
                                int length = Array.getLength(criterion.getValue());
                                for (int i = 0; i < length; i++) {
                                    Object o = Array.get(criterion.getValue(), i);
                                    parameters.add(getSQLParameterByUseBasicType(useBasicType, o , criterion.getColumn()));
                                }
                                break;
                            case listValue:
                                sb.append(getSqlFragment(example, criterion, useAlias));
                                for (Object obj : (Collection<?>) criterion.getValue()) {
                                    parameters.add(getSQLParameterByUseBasicType(useBasicType, obj, criterion.getColumn()));
                                }
                                break;
                            default:
                                break;
                        }
                    }
                    if (flag) {
                        sb.append(")");
                    }
                    flag = true;
                }
            }
        }
        String content = sb.toString().trim();
        if (StringUtils.startsWithIgnoreCase(content, "and")) {
            content = content.substring(content.indexOf("and") + 3);
        } else if (StringUtils.startsWithIgnoreCase(content, "or")) {
            content = content.substring(content.indexOf("or") + 2);
        }//排除开头的and或or
        if (example.whereWrapper) {
            content = SimpleSQLTemplate.unwrapper(example.dialect, content);
        }
        context.setSql(content);
        context.setParas(parameters);
        return context;
    }

    private static String getSqlFragment(Example example, Example.Criterion criteria, boolean useAlias) {
        DBDialect dbDialect = example.dialect;
        if (criteria.getColumn() != null) {
            if (dbDialect != null) {
                return example.getColumnAlias(dbDialect.wrapKeyword(criteria.getColumn().getRawColumnName()), useAlias) + criteria.getCondition();
            }
            return example.getColumnAlias(criteria.getColumn().getRawColumnName(), useAlias) + criteria.getCondition();
        }
        return criteria.getCondition();
    }

    public static String getGroupByClause(Example example, boolean useAlias) {
        String groupBySql = example.getGroupByClause(useAlias);
        if (groupBySql != null && groupBySql.length() > 0) {
            return " group by " + groupBySql;
        }
        return "";
    }

    public static String getOrderByClause(Example example, boolean useAlias) {
        String orderBySql = example.getOrderByClause(useAlias);
        if (orderBySql != null && orderBySql.length() > 0) {
            return " order by " + orderBySql;
        }
        return "";
    }

    public static SQLParameter getSQLParameterByUseBasicType(boolean useBasicType, Object value, ColumnMapping mapping) {
        if (useBasicType) {
            return new SQLParameter(value, null);
        } else {
            return new SQLParameter(value, mapping);
        }
    }
}
